﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
///Class1 的摘要说明
/// </summary>
public class DataBaseClass
{

	public DataBaseClass()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}

	/// <summary>
	/// 更新方法
	/// </summary>
	/// <param name="name">属性名字字段数组</param>
	/// <param name="value">属性值数组</param>
	/// <param name="tableName">操作数据表的名字</param>
	/// <param name="conStr">连接字符串</param>
	public static bool Update(string[] name, string[] value, string tableName, string conStr)
	{
		//sql语句中含属性名字段的部分字符串
		string partialStr = "";
		// Create connection, command
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;

		for (int i = 1; i < name.Length; i++)
		{
			if (i != 1)
				partialStr += ",";
			partialStr = partialStr + name[i] + "=@" + name[i];
		}
		cmd.CommandText = "UPDATE " + tableName + "SET "
			+ partialStr + " WHERE " + name[0] + "=@" + name[0];

		// Add parameters
		for (int i = 0; i < name.Length; i++)
		{
			cmd.Parameters.AddWithValue("@" + name[i], value[i]);
		}
		// Execute command
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}

	/// <summary>
	/// 根据提供的连接字符串
	/// </summary>
	/// <param name="conStr">连接字符串</param>
	/// <returns></returns>
	public static SqlCommand GetSQLCommand(string conStr)
	{
		SqlConnection con = new SqlConnection(conStr);

		// Create command
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = con;
		return cmd;
	}
	/// <summary>
	/// 更新数据库
	/// </summary>
	/// <param name="sql">SQL语句</param>
	/// <param name="conStr">连接字符串</param>
	public static bool Update(string sql, string conStr)
	{
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = sql;
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}


	/// <summary>
	/// 删除方法
	/// </summary>
	/// <param name="idName">ID属性名</param>
	/// <param name="id">删除数据的ID值</param>
	/// <param name="tableName">操作数据表的名字</param>
	/// <param name="conStr">连接字符串</param>
	public static bool deleteData(string idName, int id, string tableName, string conStr)
	{
		// Create connection
		//SqlConnection con = new SqlConnection(conStr);

		//// Create command
		//SqlCommand cmd = new SqlCommand();
		//cmd.Connection = con;
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = "DELETE " + tableName + " WHERE " + idName + "=@" + idName;

		// Add parameters
		cmd.Parameters.AddWithValue("@" + idName, id);

		// Execute command
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}
	public static bool deleteData(string sql, string conStr)
	{
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = sql;
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}

	//插入方法，传入参数依次为属性名字字段数组、属性值数组、操作数据表的名字、连接字符串
	/// <summary>
	/// 插入数据
	/// </summary>
	/// <param name="value">表全套属性的值</param>
	/// <param name="tablename">表的名字</param>
	/// <param name="conStr">连接字符串</param>
	public static bool insertData(string[] value, string tablename, string conStr)
	{
		string sql = "INSERT INTO " + tablename + " VALUES (";
		for (int i = 1; i <= value.Length; ++i)
		{
			if (i != 1)
				sql += ",";
			sql += "@" + i.ToString();
		}
		sql += ")";

		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = sql;
		for (int i = 1; i <= value.Length; ++i)
		{
			cmd.Parameters.AddWithValue("@" + i.ToString(), value[i - 1]);
		}
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}

	/// <summary>
	/// 插入方法
	/// </summary>
	/// <param name="name">属性名字字段数组</param>
	/// <param name="value">属性值数组</param>
	/// <param name="tablename">操作数据表的名字</param>
	/// <param name="conStr">连接字符串</param>
	public static bool insertData(string[] name, object[] value, string tablename, string conStr)
	{
		string sql = "INSERT INTO " + tablename + "(";

		for (int i = 0; i < value.Length; ++i)
		{
			if (i != 0)
				sql += ",";
			sql +=  name[i];
		}
		sql +=") VALUES (";
		for (int i = 0; i < value.Length; ++i)
		{
			if (i != 0)
				sql += ",";
			sql += "@" + name[i];
		}
		sql += ")";
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = sql;
		for (int i = 0; i < value.Length; ++i)
		{
			cmd.Parameters.AddWithValue("@" + name[i], value[i]);
		}
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}
	public static bool insertData(string sql, string conStr)
	{
		SqlCommand cmd = GetSQLCommand(conStr);
		SqlConnection con = cmd.Connection;
		cmd.CommandText = sql;
		int count = 0;
		using (con)
		{
			con.Open();
			count = cmd.ExecuteNonQuery();
		}

		if (count > 0)
			return true;
		else
			return false;//判断是否成功
	}

	/// <summary>
	/// 查询方法
	/// </summary>
	/// <param name="name">属性名字字段数组</param>
	/// <param name="condition">查询条件，没有WHERE</param>
	/// <param name="tablename">操作数据表的名字</param>
	/// <param name="conStr">连接字符串</param>
	/// <returns>查询得到的DataTable对象</returns>
	public static DataTable selectData(string[] name, string condition, string tablename, string conStr)
	{
		string sql = "SELECT ";
		if (name == null || name.Length == 0)
			sql += "* ";
		else
		{
			for (int i = 0; i < name.Length; ++i)
			{
				if (i != 0)
					sql += ",";
				sql += name[i];
			}
		}
		sql += " FROM " + tablename;
		if (condition != null && condition != "")
			sql += " WHERE " + condition;
		return selectData(sql, conStr);
	}
	public static DataTable selectData(string sql, string conStr)
	{
		SqlDataAdapter dad = new SqlDataAdapter(sql, conStr);
		SqlCommandBuilder builder = new SqlCommandBuilder(dad);

		DataTable dtblMovies = new DataTable();
		dad.Fill(dtblMovies);
		return dtblMovies;
	}
	public static string GetConnectionString()
	{
		return "Data Source = .\\SQLEXPRESS;Integrated Security = true; AttachDbFilename = |DataDirectory|\\MOVIE.mdf;User Instance = true;"; ;
	}
	public static int GetMaxID(string tablename , string conStr)
	{
		DataTable table = selectData("SELECT MAX(ID) AS ID FROM "+ tablename , conStr);
		if(table.Rows.Count == 0)
			return -1;
		return (int)table.Rows[0]["ID"];
	}
	public static DataTable selectPatialData(string sql, int start, int size, string conStr)
	{
		SqlDataAdapter dad = new SqlDataAdapter(sql, conStr);
		SqlCommandBuilder builder = new SqlCommandBuilder(dad);

		DataTable dtblMovies = new DataTable();
		dad.Fill(start, size, dtblMovies);
		return dtblMovies;
	}
}


